# -*- coding:utf-8 -*-
#!/usr/bin/python3

# -*- coding: UTF-8 -*-
import time
import smtplib
from email.mime.text import MIMEText
from email.header import Header


import time
import datetime
import mysql.connector

# 创建链接数据库
config1 = {'host': '192.168.1.101',  # 默认127.0.0.1
          'user': 'root', 'password': 'Ccii@562>c', 'port': 3306,  # 默认即为3306
          'database': 'ccii_redesign', 'charset': 'utf8'  # 默认即为utf8
          }

config2 = {'host': 'rm-2zefd6473rz062234ro.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'manager_lisha', 'password': '(_X(8QN42FU4!YG_et32', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }


config = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

# 查询数据库列表
def findUserList(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select id, mobile, created_date from shop_user where is_enabled = 1 and  DATE_FORMAT(created_date,"%Y-%m-%d") = %s order by id desc '
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
总用户数-有效用户数
查询无效的用户数据记录-未活跃和总记录减去该值
AND del_flag = 0
AND role_type = 2
AND dtype = "Member"
'''
def findUserListByIsAll(isAll, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select id, mobile from shop_user where del_flag = 0 and  DATE_FORMAT(created_date,"%Y-%m-%d") < %s '
        if not isAll:
            stmt += " and role_type = 2 AND dtype = 'Member' order by id desc "

        print(date.strftime('%Y-%m-%d'))
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询活跃用户数据
'''
def findActiveUserList(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        # stmt = 'select id, mobile, created_date from shop_user where  DATE_FORMAT(created_date,"%Y-%m-%d") = %s order by id desc '
        stmt = 'SELECT COUNT(*), days FROM data_user_active_detail WHERE DATE_FORMAT(create_date,"%Y-%m-%d") = %s GROUP BY days ORDER BY days'
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询1天活跃的用户总数据
'''
def findActiveUserTotal(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'SELECT  COUNT(*) FROM data_user_active_detail WHERE DATE_FORMAT(create_date,"%Y-%m-%d") = %s AND type = 1'
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询1天活跃的用户总数据
'''
def findMemberGradeIdTotal(memberGradeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
        
                SELECT
                    COUNT(*)
                FROM
                    shop_company sc
                LEFT JOIN shop_user su ON sc.user_id = su.id
                WHERE
                role_type = 2
                AND su.del_flag = 0
                AND dtype = 'Member' 
                AND member_grade_id = %s
                AND DATE_FORMAT(su.created_date, "%Y-%m-%d") < %s
                
                """
        cursor.execute(stmt, params=(memberGradeId, date))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询企业注册店铺数量
'''
def findStoreList(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT
                        sp.store_id,
                        sp.created_date
                    FROM
                        shop_product sp
                    LEFT JOIN shop_store ss ON sp.store_id = ss.id
                    LEFT JOIN shop_user su ON ss.business_id = su.id
                    LEFT JOIN shop_company sc ON su.company_id = sc.id
                    WHERE
                        sc.examine_type = 1
                    AND DATE_FORMAT(sc.examine_date, '%Y-%m-%d') = %s
                    GROUP BY
                        sp.store_id 
                """

        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询企业开店店铺总数量-上传执照审核通过-上传过商品
'''
def findStoreAllList():
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt =  """
                SELECT
                    sp.store_id,
                    sp.created_date
                FROM
                    shop_product sp
                LEFT JOIN shop_store ss ON sp.store_id = ss.id
                LEFT JOIN shop_user su ON ss.business_id = su.id
                LEFT JOIN shop_company sc ON su.company_id = sc.id
                WHERE
                    sc.examine_type = 1
                GROUP BY
                    sc.com_name
                """

        cursor.execute(stmt, params=())
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询个人注册店铺数量
'''
def findPersionalStoreList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT
                       	sp.store_id,
	                    sp.created_date
                    FROM
                        shop_product sp
                    LEFT JOIN shop_store ss ON sp.store_id = ss.id
                    LEFT JOIN shop_user su ON ss.business_id = su.id
                    LEFT JOIN shop_company sc ON su.company_id = sc.id
                    WHERE
                        1 = 1
                   
                """
        # su.is_realname = 0

        params = ()

        if not isAll:
            params = (date, )
            stmt += '''
                     AND DATE_FORMAT(sc.examine_date, '%Y-%m-%d') = %s
                     
                    '''

        stmt += " GROUP BY sp.store_id "

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询圈子发布数
'''
def findCircleList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_circle
                WHERE
                    del_flag = 0
                
               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(create_date, "%Y-%m-%d") = %s '''
            params = (date, )

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询圈子更新数
'''

def findCircleUpdateList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_circle
                WHERE
                    del_flag = 0

               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(top_update_date, "%Y-%m-%d") = %s '''
            params = (date,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询商品发布数
'''
def findProductList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_product
                WHERE
                    is_list = 1
                AND is_active = 1
                AND is_marketable = 1

               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(created_date, "%Y-%m-%d") = %s '''
            params = (date,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询商品更新数量
'''
def findProductUpdateList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_product
                WHERE
                    is_list = 1
                AND is_active = 1
                AND is_marketable = 1

               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(last_modified_date, "%Y-%m-%d") = %s '''
            params = (date,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询需求发布数
'''
def findDemandList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_supply_demand
                WHERE
                    del_flag = 0
                AND type = 1 
                AND enable = 0
                
               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(create_date, "%Y-%m-%d") = %s '''
            params = (date,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询需求更新数
'''

def findDemandUpdateList(date, isAll):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    shop_supply_demand
                WHERE
                    del_flag = 0
                AND type = 1 
                AND enable = 0

               """
        params = ()
        if not isAll:
            stmt += ''' AND DATE_FORMAT(update_date, "%Y-%m-%d") = %s '''
            params = (date,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
查询开通支付申请数量
status  2、待处理；3、申请中；4、开通成功；5、开通失败
'''

def findStoreBankList(date, status):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(id)
                FROM
                    store_bank_info
                WHERE 1 = 1

               """
        params = ()

        if not date == '':
            stmt += ''' AND  DATE_FORMAT(create_date, "%Y-%m-%d") = %s '''
            params = (date,)

        if not status == '':
            stmt += ''' AND  status = %s '''
            params = (status,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询平安支付开通数据
'''
def findPingAnPayOpenList(delFlag):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(*)
                FROM
                    pay_pingan_user_info
                WHERE
                    1 = 1

               """
        params = ()

        if not delFlag == '':
            stmt += ''' AND  del_flag = %s '''
            params = (delFlag,)

        cursor.execute(stmt, params=params)
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
保存邮件数据内容到数据库
'''

def saveEmailContent(content = ''):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        item = []
        item.append(content)
        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into data_email_report(content,create_date, type, del_flag) values (%s, NOW(), 0, 0)'
        cursor.execute(stmt, item)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接



# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset)\
        # .strftime('%Y-%m-%d')
    # print(re_date)
    return re_date


'''
获取注册用户数数量html
'''
def getRegisterUserHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    todayDate = getdate(0)

    content = "<span style='font-size:16px;color:#e92e2e;'>数据生成日期：" + todayDate.strftime('%Y-%m-%d') + "</span><br/>"
    content += "<br/>近8天会员注册量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr><tr><td style='text-align:center'  >数量</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + str(len(findUserList(date.strftime('%Y-%m-%d')))) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取每近8天活跃用户数量
'''
def getActiveUserHtml(dayNum):

    # 昨天日期yyyy-mm-dd
    yesterday = getdate(dayNum)
    data = findActiveUserList(yesterday)
    print(data)

    tableTitle = "<br/>用户连续活跃数据:<br/>"
    content = "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>天数</td>"
    total = 0
    # 连续活跃超过7天的数量统计
    largerSeven = 0

    userTotal = len(findUserListByIsAll(True, getdate(0)))
    validUserTotal = len(findUserListByIsAll(False, getdate(0)))
    # 减去45个测试用户数量
    testUserTotal = 45
    # 无效用户=总用户-有效用户数
    unValidUserTotal = userTotal - validUserTotal
    # unValidUserTotal = 107
    # print("userTotal=" + str(userTotal) + "----validUserTotal=" + str(validUserTotal) + "----unValidUserTotal=" + str(unValidUserTotal))

    # [(2101, 0), (56, 1), (76, 2)]
    for item in data:
        if item[1] > 7:
            largerSeven+= item[0]
        else:
            content += "<td style='text-align:center' >" + str(item[1]) + " 天</td>"

    if largerSeven > 0:
        content+= "<td style='text-align:center'>7天以上</td>"

    content+= "<td style='text-align:center'>注册总数</td>"
    content+="</tr><tr><td style='text-align:center'>数量</td>"

    for index, item in enumerate(data):
        if index == 0:
            total += item[0] - unValidUserTotal
            content += "<td style='text-align:center' >" + str(item[0] - unValidUserTotal) + " </td>"
        else:
            total += item[0]
            if item[1] < 8:
                content += "<td style='text-align:center' >" + str(item[0]) + " </td>"


    if largerSeven > 0:
        content += "<td style='text-align:center' >" + str(largerSeven) + " </td>"

    print("total=" + str(total))
    content += "<td style='text-align:center' >" + str(total - testUserTotal) + " </td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    # print(content)
    return tableTitle + content


'''
获取近8天每天活跃用户数据
'''
def getActiveUserOneDayTotalHtml():

    #sql实际应查询的日期
    date_sql = [7, 6, 5, 4, 3, 2, 1, 0]
    date_num = [8, 7, 6, 5, 4, 3, 2, 1]
    date_arr = []
    sql_date_arr = []
    for index, num in enumerate(date_num):
        date_arr.append(getdate(num))
        sql_date_arr.append(getdate(date_sql[index]))

    data = []
    for day in sql_date_arr:
        data.append(findActiveUserTotal(day))
    print(data)

    tableTitle = "<br/>近8天每天活跃用户数据:<br/>"
    content = "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for item in date_arr:
        content += "<td style='text-align:center' >" + str(item.strftime('%m-%d')) + "</td>"

    content+="</tr><tr><td style='text-align:center'>数量</td>"

    for item in data:
        content += "<td style='text-align:center' >" + str(item[0][0]) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    print(content)
    return tableTitle + content


'''
获取各等级会员总数据
'''
def getMemberGradeHtml():

    #sql实际应查询的日期
    grade_ids = [1, 2, 3, 4, 5]
    member_names = ['注册会员', '试用会员', '基础会员', '行情通', '买卖通']

    date = getdate(0)
    data = []
    for memberId in grade_ids:
        data.append(findMemberGradeIdTotal(memberId, date.strftime('%Y-%m-%d')))
    print(data)

    tableTitle = "<br/>付费商户数:<br/>"
    content = "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>会员等级</td>"

    for memberName in member_names:
        content += "<td style='text-align:center' >" + str(memberName) + "</td>"

    content+="</tr><tr><td style='text-align:center'>会员数量</td>"

    for index, item in enumerate(data):
        if index == 0 :
            content += "<td style='text-align:center' >" + str(item[0][0] - 45) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str(item[0][0]) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    print(content)
    return tableTitle + content



'''
获取每天企业开店数量
'''
def getRegisterStoreHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天企业开店数量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + str(len(findStoreList(date.strftime('%Y-%m-%d')))) + "</td>"

    rsList = findStoreAllList()
    content += "<td style='text-align:center'>"+ str(len(rsList)) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取每天个人开店数量
'''
def getPersionalStoreHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天个人开店数量(包含未实名认证店铺):<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        num = 0
        rs = findPersionalStoreList(date.strftime('%Y-%m-%d'), False)
        if rs:
            num = len(rs)
        content += "<td style='text-align:center' >" + str(num) + "</td>"

    rsList = findPersionalStoreList('', True)
    content += "<td style='text-align:center'>"+ str(len(rsList)) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取近8天圈子发布数量
'''
def getCircleHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天圈子信息发布量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findCircleList(day, False)[0][0]) + "</td>"

    rsList = findCircleList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取近8天圈子更新数量
'''
def getCircleUpdateHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天圈子更新数量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findCircleUpdateList(day, False)[0][0]) + "</td>"

    rsList = findCircleUpdateList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取近8天商品发布数量
'''
def getProductHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天商品信息发布量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findProductList(day, False)[0][0]) + "</td>"

    rsList = findProductList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取近8天商品每天更新数量
'''
def getProductUpdateHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天商品更新数量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findProductUpdateList(day, False)[0][0]) + "</td>"

    rsList = findProductUpdateList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取近8天采购发布数量
'''
def getDemandHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天采购信息发布量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findDemandList(day, False)[0][0]) + "</td>"

    rsList = findDemandList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取近8天采购更新数量
'''
def getDemandUpdateHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天采购信息更新数量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findDemandUpdateList(day, False)[0][0]) + "</td>"

    rsList = findDemandUpdateList('', True)
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取近8天支付申请数量
'''
def getStoreBankHtml():

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = "<br/>近8天交易通申请数量:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for date in date_arr:
        day = date.strftime('%Y-%m-%d')
        content += "<td style='text-align:center' >" + str(findStoreBankList(day,'')[0][0]) + "</td>"

    rsList = findStoreBankList('', '')
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content



'''
获取支付开通各个状态总数量
'''
def getStoreBankStateHtml():

    state_num = ['2', '3', '4', '5']
    state_desc = ['待处理', '申请中', '开通成功', '开通失败']

    content = "<br/>开通交易通支付状态数量统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>状态</td>"

    for stateDesc in state_desc:
        content += "<td style='text-align:center' >" + stateDesc  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center'>总数量</td></tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for state in state_num:
        content += "<td style='text-align:center' >" + str(findStoreBankList('', state)[0][0]) + "</td>"

    rsList = findStoreBankList('', '')
    content += "<td style='text-align:center'>"+ str(rsList[0][0]) +"</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取平安支付开通数据
'''
def getCompanyPingAnPayStateHtml():

    state_num = ['0', '1', '']
    state_desc = ['开通成功', '解绑数量', '总数量']

    content = "<br/>开通平安支付状态数量统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='600'><tr><td style='text-align:center'>状态</td>"

    for stateDesc in state_desc:
        content += "<td style='text-align:center' >" + stateDesc  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr>"
    content+="<tr><td style='text-align:center'>数量</td>"

    for state in state_num:
        content += "<td style='text-align:center' >" + str(findPingAnPayOpenList(state)[0][0]) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content





def send_email(content):
    # 第三方 SMTP 服务
    mail_host = "smtp.exmail.qq.com"  # 设置服务器
    mail_user = "coldeal@cciigroup.com"  # 用户名
    mail_pass = "intlCCII2020"  # 口令

    sender = 'coldeal@cciigroup.com'
    receivers = ['renl@cciigroup.com', 'zhaon@cciigroup.com', 'dengpr@cciigroup.com',

                 'liyf@cciigroup.com', 'liuyn@cciigroup.com', 'zhangzl@cciigroup.com',
                 'yuy@cciigroup.com', 'majq@cciigroup.com',
                 'tianxl@cciigroup.com', 'zangm@cciigroup.com',

                 'wangzw@cciigroup.com', 'guj@cciigroup.com', 'liupy@cciigroup.com',
                 'anjp@cciigroup.com', 'hancy@cciigroup.com',
                 'lis@cciigroup.com'
                 ]
    
    # receivers = ['lis@cciigroup.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
    # 邮件正文内容
    message = MIMEText(content, 'HTML', 'utf-8')
    # 发件人名称
    message['From'] = Header("coldeal@cciigroup.com", 'utf-8')

    # 收件人名称
    # message['To'] = Header("每日会员注册量", 'utf-8')

    # 邮件名称 Python SMTP 邮件测试
    subject = '每天平台数据统计'
    message['Subject'] = Header(subject, 'utf-8')
    print("开始发送")
    try:
      smtpObj = smtplib.SMTP_SSL(mail_host)
    #smtpObj.connect(mail_host, 587)  # 25 为 SMTP 端口号
    #smtpObj.starttls()
      smtpObj.login(mail_user, mail_pass)
      for receiver in receivers:
         message['To'] = Header(receiver, 'utf-8')
         smtpObj.sendmail(sender, receiver, message.as_string())

      print("邮件发送成功")
    except smtplib.SMTPException as e:
        print("Error: 无法发送邮件")
        print(e)

'''
主调用方法
'''
def work(dayNum):
    content = ''
    content = getRegisterUserHtml(dayNum)
    content += getActiveUserHtml(dayNum - 1)
    content += getActiveUserOneDayTotalHtml()
    # content += getMemberGradeHtml()
    content += getRegisterStoreHtml()
    # content += getPersionalStoreHtml()
    # content += getCircleHtml()
    # content += getCircleUpdateHtml()
    content += getProductHtml()
    content += getProductUpdateHtml()
    content += getDemandHtml()
    content += getDemandUpdateHtml()
    # content += getStoreBankHtml()
    # content += getStoreBankStateHtml()
    content += getCompanyPingAnPayStateHtml()
    print(content)
    send_email(content)

    try:
        saveEmailContent(content)
    except mysql.connector.Error as e:
        print('保存邮件数据异常', str(e))





if __name__ == "__main__":
    work(1)

